﻿using Liu_Project.Dal.Common;
using LSL.SqlClient;
using System;
using System.Collections;
using System.Data;
using System.Text;

namespace Liu_Project.Dal.Admin
{
    public class HomeDal : DalBase
    {
        private readonly StringBuilder sb = new StringBuilder();

        public Hashtable GetUserMessageList()
        {
            var hash = new Hashtable();
            sb.Clear();
            sb.AppendLine("SELECT COUNT(1) ");
            sb.AppendLine("FROM dbo.EntUserLoginLog WITH(NOLOCK)");
            if (!CurrentUser.IsAdministrator)
                sb.AppendLine("WHERE UserId=" + CurrentUser.UserId);
            hash["LoginLogCount"] = DataConvert.ToInt(new SQLHelper().ExecuteScalar(sb.ToString()));

            sb.Clear();
            sb.AppendLine("SELECT COUNT(1) ");
            sb.AppendLine("FROM dbo.EntLog WITH(NOLOCK)");
            if (!CurrentUser.IsAdministrator)
                sb.AppendLine("WHERE UserId=" + CurrentUser.UserId);
            hash["LogCount"] = DataConvert.ToInt(new SQLHelper().ExecuteScalar(sb.ToString()));
            return hash;
        }

        public DataTable GetUserLoginList()
        {
            var sqlHelper = new SQLHelper()
            {
                IsAutoClose = false
            };
            try
            {
                var tb_Day = new DataTable();
                tb_Day.Columns.Add(new DataColumn("date", typeof(string)));
                tb_Day.Columns.Add(new DataColumn("day", typeof(int)));
                tb_Day.Columns.Add(new DataColumn("SusCount", typeof(int)));
                tb_Day.Columns.Add(new DataColumn("NoSusCount", typeof(int)));

                DateTime endTime = DateTime.Now;
                TimeSpan sp = endTime.Subtract(endTime.AddDays(-6));
                for (DateTime i = endTime.AddDays(-sp.Days); i <= endTime; i = i.AddDays(1))
                {
                    DataRow dr = tb_Day.NewRow();
                    dr["date"] = i.ToString("yyyy-MM-dd");
                    dr["day"] = i.Day;
                    dr["SusCount"] = 0;
                    dr["NoSusCount"] = 0;
                    tb_Day.Rows.Add(dr);
                }

                string TbUserLoginLog = sqlHelper.GetTempTable();
                sb.Length = 0;
                sb.AppendLine("SELECT CONVERT(VARCHAR(50),LoginTime,23) LoginTime, DAY(LoginTime) LoginDay ");
                sb.AppendLine(",CASE IsSuccess WHEN 1 THEN COUNT(AutoId) ELSE 0 END SusCount ");
                sb.AppendLine(",CASE IsSuccess WHEN 0 THEN COUNT(AutoId) ELSE 0 END NoSusCount ");
                sb.AppendLine("INTO " + TbUserLoginLog);
                sb.AppendLine("FROM EntUserLoginLog WITH(NOLOCK)");
                sb.AppendLine("WHERE DATEDIFF(DAY,LoginTime,GETDATE())<=7 AND UserId=" + CurrentUser.UserId);
                sb.AppendLine("GROUP BY CONVERT(VARCHAR(50),LoginTime,23),DAY(LoginTime),IsSuccess");
                sqlHelper.ExecuteNonQuery(sb.ToString());

                sb.Length = 0;
                sb.AppendLine("SELECT LoginTime,LoginDay,MAX(SusCount) SusCount,MAX(NoSusCount) NoSusCount");
                sb.AppendLine("FROM " + TbUserLoginLog);
                sb.AppendLine("GROUP BY LoginTime,LoginDay");
                var tbLoginDay = sqlHelper.ExecuteDataTable(sb.ToString());

                foreach (DataRow row in tb_Day.Rows)
                {
                    foreach (DataRow item in tbLoginDay.Rows)
                    {
                        if (row["day"].ToString() == item["LoginDay"].ToString())
                        {
                            row["SusCount"] = item["SusCount"];
                            row["NoSusCount"] = item["NoSusCount"];
                            break;
                        }
                    }
                }

                return tb_Day;
            }
            finally
            {
                sqlHelper.Close();
            }
        }

    }
}
